{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4146a9ec-259d-493b-906b-99b86a7465aa",
   "metadata": {},
   "source": [
    "## 1.Pandas的进阶处理\n",
    "### 1.1、文件的读取和查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9d67dba7-b408-4fff-9c31-11d96da2643c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "****************************************************************************************************\n",
      "(121, 17)\n",
      "Index(['student_id', 'name', 'gender', 'class', 'age', 'register_date', 'city',\n",
      "       'club', 'Chinese', 'Math', 'English', 'Physics', 'Chemistry', 'Biology',\n",
      "       'absence_count', 'height_cm', 'weight_kg'],\n",
      "      dtype='object')\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 121 entries, 0 to 120\n",
      "Data columns (total 17 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   student_id     121 non-null    int64  \n",
      " 1   name           121 non-null    object \n",
      " 2   gender         121 non-null    object \n",
      " 3   class          121 non-null    object \n",
      " 4   age            121 non-null    int64  \n",
      " 5   register_date  121 non-null    object \n",
      " 6   city           101 non-null    object \n",
      " 7   club           102 non-null    object \n",
      " 8   Chinese        115 non-null    float64\n",
      " 9   Math           112 non-null    float64\n",
      " 10  English        121 non-null    float64\n",
      " 11  Physics        121 non-null    float64\n",
      " 12  Chemistry      121 non-null    float64\n",
      " 13  Biology        121 non-null    float64\n",
      " 14  absence_count  120 non-null    float64\n",
      " 15  height_cm      121 non-null    float64\n",
      " 16  weight_kg      121 non-null    float64\n",
      "dtypes: float64(9), int64(2), object(6)\n",
      "memory usage: 16.2+ KB\n",
      "None\n",
      "         student_id         age     Chinese        Math  ...     Biology  absence_count   height_cm   weight_kg\n",
      "count    121.000000  121.000000  115.000000  112.000000  ...  121.000000     120.000000  121.000000  121.000000\n",
      "mean   23060.090909   10.330579   80.170435   80.370536  ...   80.383471       2.008333  154.518182   41.542149\n",
      "std       34.930884    0.934420    8.977525   11.157364  ...    7.509165       1.356564    7.155685    6.052806\n",
      "min    23001.000000    9.000000   58.800000   54.600000  ...   59.800000       0.000000  139.200000   19.100000\n",
      "25%    23030.000000   10.000000   74.200000   71.875000  ...   75.500000       1.000000  150.100000   38.600000\n",
      "50%    23060.000000   10.000000   79.300000   81.900000  ...   80.800000       2.000000  154.100000   41.100000\n",
      "75%    23090.000000   11.000000   85.500000   88.125000  ...   85.400000       3.000000  159.900000   45.500000\n",
      "max    23120.000000   12.000000  105.000000  105.100000  ...  103.100000       6.000000  168.900000   55.600000\n",
      "\n",
      "[8 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('data/students_raw_utf8.csv',encoding='utf-8')\n",
    "df.head()#看前5行\n",
    "df.head(10)#看前十行\n",
    "df.tail(10)#看后十行\n",
    "df.sample(5,random_state=100)#随机抽样\n",
    "print('*'*100)\n",
    "#了解数据的自身情况\n",
    "print(df.shape)\n",
    "print(df.columns)\n",
    "print(df.info())\n",
    "print(df.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1345927e-b321-4153-b0bd-74780621e334",
   "metadata": {},
   "source": [
    "### 1.2、读取其他表的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "dbbec1bb-c75a-4054-af5a-5af98c1d5ec4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "昭通          滇东北\n",
       "昆明           滇中\n",
       "曲靖          滇东北\n",
       "zhaotong    滇东北\n",
       "Kunming      滇中\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('data/students_raw_utf8.csv',\n",
    "                 encoding='utf-8',\n",
    "                 usecols=['student_id','name','class','Chinese','Math','English'],\n",
    "                nrows=50)\n",
    "#读取班级信息\n",
    "class_df = pd.read_csv('./data/class_info_utf8.csv')\n",
    "class_df.head()\n",
    "#读取健康数据\n",
    "health_df = pd.read_excel('./data/students_health_utf8.xlsx')\n",
    "health_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6cb0a8d-c895-4208-aa85-aaeedfef2e3e",
   "metadata": {},
   "source": [
    "合并读取的数据\n",
    "pd.merge(A, B, on='共同列名', how='合并方式')\n",
    "| 合并方式      | 含义                |\n",
    "| --------- | ----------------- |\n",
    "| **inner** | 只保留双方都有的记录（交集）    |\n",
    "| **left**  | 保留左表全部，右表能匹配多少算多少 |\n",
    "| **right** | 保留右表全部            |\n",
    "| **outer** | 双方所有记录（并集）        |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f190e23-41e4-467c-a4f6-6e86c5d5e8d8",
   "metadata": {},
   "source": [
    "### 1.3、合并数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "a1a55a5d-3cbf-4560-81dc-1082f9e505fa",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>class</th>\n",
       "      <th>head_teacher</th>\n",
       "      <th>room</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2A</td>\n",
       "      <td>孔老师</td>\n",
       "      <td>2号楼-367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2B</td>\n",
       "      <td>张老师</td>\n",
       "      <td>2号楼-257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3A</td>\n",
       "      <td>王老师</td>\n",
       "      <td>3号楼-370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3B</td>\n",
       "      <td>王老师</td>\n",
       "      <td>3号楼-351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4A</td>\n",
       "      <td>王老师</td>\n",
       "      <td>4号楼-347</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  class head_teacher     room\n",
       "0    2A          孔老师  2号楼-367\n",
       "1    2B          张老师  2号楼-257\n",
       "2    3A          王老师  3号楼-370\n",
       "3    3B          王老师  3号楼-351\n",
       "4    4A          王老师  4号楼-347"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   student_id  height_cm  weight_kg    BMI\n",
      "0       23001      141.1       43.4  21.80\n",
      "1       23002      142.5       34.8  17.14\n",
      "2       23003      140.5       37.9  19.20\n",
      "3       23004      164.0       47.3  17.59\n",
      "4       23005      160.2       41.1  16.01\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>gender</th>\n",
       "      <th>class</th>\n",
       "      <th>age</th>\n",
       "      <th>register_date</th>\n",
       "      <th>city</th>\n",
       "      <th>club</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Math</th>\n",
       "      <th>English</th>\n",
       "      <th>Physics</th>\n",
       "      <th>Chemistry</th>\n",
       "      <th>Biology</th>\n",
       "      <th>absence_count</th>\n",
       "      <th>head_teacher</th>\n",
       "      <th>room</th>\n",
       "      <th>BMI</th>\n",
       "      <th>height_cm</th>\n",
       "      <th>weight_kg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>1111</td>\n",
       "      <td>测试91</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>2222</td>\n",
       "      <td>测试22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     student_id  name gender class  age register_date city club  Chinese  \\\n",
       "123        1111  测试91    NaN   NaN  NaN           NaN  NaN  NaN      NaN   \n",
       "124        2222  测试22    NaN   NaN  NaN           NaN  NaN  NaN      NaN   \n",
       "\n",
       "     Math  English  Physics  Chemistry  Biology  absence_count head_teacher  \\\n",
       "123   NaN      NaN      NaN        NaN      NaN            NaN          NaN   \n",
       "124   NaN      NaN      NaN        NaN      NaN            NaN          NaN   \n",
       "\n",
       "    room  BMI  height_cm  weight_kg  \n",
       "123  NaN  NaN        NaN        NaN  \n",
       "124  NaN  NaN        NaN        NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('data/students_raw_utf8.csv',\n",
    "                 encoding='utf-8')\n",
    "#读取班级信息\n",
    "class_df = pd.read_csv('./data/class_info_utf8.csv')\n",
    "display(class_df.head())\n",
    "#读取健康数据\n",
    "health_df = pd.read_excel('./data/students_health_utf8.xlsx')\n",
    "print(health_df.head())\n",
    "#合并两张表\n",
    "df1 = pd.merge(df,class_df,on='class',how='left')\n",
    "df1\n",
    "#为df加入一些有问题的数据\n",
    "ed = [{'student_id':1111,'name':'测试91'},{'student_id':2222,'name':'测试22'}]\n",
    "df2 = pd.concat([df,pd.DataFrame(ed)])\n",
    "df2\n",
    "#左表中的所有数据都会显示\n",
    "df3 = pd.merge(df2,class_df,on='class',how='left')\n",
    "df3\n",
    "#合并健康数据\n",
    "df4 = pd.merge(df3,health_df,on='student_id',how='left')\n",
    "df4\n",
    "#由于两张表中都有height_cm，pandas会自动加上_x和_y来标识\n",
    "df4[['height_cm','weight_kg']] = df4[['height_cm_x','weight_kg_y']]\n",
    "df4 = df4.drop(columns=['height_cm_x','height_cm_y','weight_kg_x','weight_kg_y'])\n",
    "df4\n",
    "display(df4[df4['height_cm'].isna()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8b3d52d-641b-4b7a-b54b-dd5d636e9913",
   "metadata": {},
   "source": [
    "在df4的基础上处理城市和区域"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "32900176-1042-41b6-bece-dba6fdac97fb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'昭通': '滇东北', '昆明': '滇中', '曲靖': '滇东北', 'zhaotong': '滇东北', 'Kunming': '滇中'} dict_items([('昭通', '滇东北'), ('昆明', '滇中'), ('曲靖', '滇东北'), ('zhaotong', '滇东北'), ('Kunming', '滇中')])\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>gender</th>\n",
       "      <th>class</th>\n",
       "      <th>age</th>\n",
       "      <th>register_date</th>\n",
       "      <th>city</th>\n",
       "      <th>club</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Math</th>\n",
       "      <th>...</th>\n",
       "      <th>Physics</th>\n",
       "      <th>Chemistry</th>\n",
       "      <th>Biology</th>\n",
       "      <th>absence_count</th>\n",
       "      <th>head_teacher</th>\n",
       "      <th>room</th>\n",
       "      <th>BMI</th>\n",
       "      <th>height_cm</th>\n",
       "      <th>weight_kg</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23001</td>\n",
       "      <td>褚超琪</td>\n",
       "      <td>M</td>\n",
       "      <td>2B</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2024-09-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>合唱</td>\n",
       "      <td>83.2</td>\n",
       "      <td>104.5</td>\n",
       "      <td>...</td>\n",
       "      <td>103.4</td>\n",
       "      <td>106.1</td>\n",
       "      <td>77.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>张老师</td>\n",
       "      <td>2号楼-257</td>\n",
       "      <td>21.80</td>\n",
       "      <td>141.1</td>\n",
       "      <td>43.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23002</td>\n",
       "      <td>孙明丽</td>\n",
       "      <td>男</td>\n",
       "      <td>2B</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2024-08-25</td>\n",
       "      <td>昆明</td>\n",
       "      <td>篮球</td>\n",
       "      <td>74.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>67.7</td>\n",
       "      <td>87.8</td>\n",
       "      <td>81.7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>张老师</td>\n",
       "      <td>2号楼-257</td>\n",
       "      <td>17.14</td>\n",
       "      <td>142.5</td>\n",
       "      <td>34.8</td>\n",
       "      <td>滇中</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23003</td>\n",
       "      <td>冯鑫芸</td>\n",
       "      <td>M</td>\n",
       "      <td>2A</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2024-08-07</td>\n",
       "      <td>昆明</td>\n",
       "      <td>篮球</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58.5</td>\n",
       "      <td>...</td>\n",
       "      <td>69.1</td>\n",
       "      <td>57.5</td>\n",
       "      <td>83.4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>孔老师</td>\n",
       "      <td>2号楼-367</td>\n",
       "      <td>19.20</td>\n",
       "      <td>140.5</td>\n",
       "      <td>37.9</td>\n",
       "      <td>滇中</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23004</td>\n",
       "      <td>孔凯霖</td>\n",
       "      <td>女</td>\n",
       "      <td>3B</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2023-12-15</td>\n",
       "      <td>zhaotong</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76.7</td>\n",
       "      <td>69.7</td>\n",
       "      <td>...</td>\n",
       "      <td>77.2</td>\n",
       "      <td>71.1</td>\n",
       "      <td>71.9</td>\n",
       "      <td>4.0</td>\n",
       "      <td>王老师</td>\n",
       "      <td>3号楼-351</td>\n",
       "      <td>17.59</td>\n",
       "      <td>164.0</td>\n",
       "      <td>47.3</td>\n",
       "      <td>滇东北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23005</td>\n",
       "      <td>杨旭豪</td>\n",
       "      <td>女</td>\n",
       "      <td>4B</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2024-03-10</td>\n",
       "      <td>zhaotong</td>\n",
       "      <td>足球</td>\n",
       "      <td>67.9</td>\n",
       "      <td>66.8</td>\n",
       "      <td>...</td>\n",
       "      <td>65.6</td>\n",
       "      <td>56.6</td>\n",
       "      <td>75.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>李老师</td>\n",
       "      <td>4号楼-279</td>\n",
       "      <td>16.01</td>\n",
       "      <td>160.2</td>\n",
       "      <td>41.1</td>\n",
       "      <td>滇东北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>23120</td>\n",
       "      <td>赵宁睿</td>\n",
       "      <td>M</td>\n",
       "      <td>4A</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2024-04-09</td>\n",
       "      <td>Kunming</td>\n",
       "      <td>足球</td>\n",
       "      <td>84.7</td>\n",
       "      <td>72.2</td>\n",
       "      <td>...</td>\n",
       "      <td>67.8</td>\n",
       "      <td>72.3</td>\n",
       "      <td>81.6</td>\n",
       "      <td>3.0</td>\n",
       "      <td>王老师</td>\n",
       "      <td>4号楼-347</td>\n",
       "      <td>14.07</td>\n",
       "      <td>166.7</td>\n",
       "      <td>39.1</td>\n",
       "      <td>滇中</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>23011</td>\n",
       "      <td>孔雯璐</td>\n",
       "      <td>M</td>\n",
       "      <td>2A</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>zhaotong</td>\n",
       "      <td>编程</td>\n",
       "      <td>90.0</td>\n",
       "      <td>64.7</td>\n",
       "      <td>...</td>\n",
       "      <td>68.4</td>\n",
       "      <td>68.4</td>\n",
       "      <td>92.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>孔老师</td>\n",
       "      <td>2号楼-367</td>\n",
       "      <td>13.48</td>\n",
       "      <td>155.3</td>\n",
       "      <td>32.5</td>\n",
       "      <td>滇东北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>23011</td>\n",
       "      <td>孔雯璐</td>\n",
       "      <td>M</td>\n",
       "      <td>2A</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>zhaotong</td>\n",
       "      <td>编程</td>\n",
       "      <td>90.0</td>\n",
       "      <td>64.7</td>\n",
       "      <td>...</td>\n",
       "      <td>68.4</td>\n",
       "      <td>68.4</td>\n",
       "      <td>92.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>孔老师</td>\n",
       "      <td>2号楼-367</td>\n",
       "      <td>13.48</td>\n",
       "      <td>155.3</td>\n",
       "      <td>32.5</td>\n",
       "      <td>滇东北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>1111</td>\n",
       "      <td>测试91</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>2222</td>\n",
       "      <td>测试22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>125 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     student_id   name gender class   age register_date      city club  \\\n",
       "0         23001    褚超琪      M    2B   9.0    2024-09-28       NaN   合唱   \n",
       "1         23002    孙明丽      男    2B   9.0    2024-08-25        昆明   篮球   \n",
       "2         23003   冯鑫芸       M    2A  10.0    2024-08-07        昆明   篮球   \n",
       "3         23004    孔凯霖      女    3B  10.0    2023-12-15  zhaotong  NaN   \n",
       "4         23005    杨旭豪      女    4B  11.0    2024-03-10  zhaotong   足球   \n",
       "..          ...    ...    ...   ...   ...           ...       ...  ...   \n",
       "120       23120    赵宁睿      M    4A  12.0    2024-04-09   Kunming   足球   \n",
       "121       23011    孔雯璐      M    2A  10.0    2024-09-23  zhaotong   编程   \n",
       "122       23011    孔雯璐      M    2A  10.0    2024-09-23  zhaotong   编程   \n",
       "123        1111   测试91    NaN   NaN   NaN           NaN       NaN  NaN   \n",
       "124        2222   测试22    NaN   NaN   NaN           NaN       NaN  NaN   \n",
       "\n",
       "     Chinese   Math  ...  Physics  Chemistry  Biology  absence_count  \\\n",
       "0       83.2  104.5  ...    103.4      106.1     77.1            1.0   \n",
       "1       74.5    NaN  ...     67.7       87.8     81.7            4.0   \n",
       "2        NaN   58.5  ...     69.1       57.5     83.4            3.0   \n",
       "3       76.7   69.7  ...     77.2       71.1     71.9            4.0   \n",
       "4       67.9   66.8  ...     65.6       56.6     75.8            2.0   \n",
       "..       ...    ...  ...      ...        ...      ...            ...   \n",
       "120     84.7   72.2  ...     67.8       72.3     81.6            3.0   \n",
       "121     90.0   64.7  ...     68.4       68.4     92.9            2.0   \n",
       "122     90.0   64.7  ...     68.4       68.4     92.9            2.0   \n",
       "123      NaN    NaN  ...      NaN        NaN      NaN            NaN   \n",
       "124      NaN    NaN  ...      NaN        NaN      NaN            NaN   \n",
       "\n",
       "     head_teacher     room    BMI  height_cm  weight_kg  region  \n",
       "0             张老师  2号楼-257  21.80      141.1       43.4     NaN  \n",
       "1             张老师  2号楼-257  17.14      142.5       34.8      滇中  \n",
       "2             孔老师  2号楼-367  19.20      140.5       37.9      滇中  \n",
       "3             王老师  3号楼-351  17.59      164.0       47.3     滇东北  \n",
       "4             李老师  4号楼-279  16.01      160.2       41.1     滇东北  \n",
       "..            ...      ...    ...        ...        ...     ...  \n",
       "120           王老师  4号楼-347  14.07      166.7       39.1      滇中  \n",
       "121           孔老师  2号楼-367  13.48      155.3       32.5     滇东北  \n",
       "122           孔老师  2号楼-367  13.48      155.3       32.5     滇东北  \n",
       "123           NaN      NaN    NaN        NaN        NaN     NaN  \n",
       "124           NaN      NaN    NaN        NaN        NaN     NaN  \n",
       "\n",
       "[125 rows x 21 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "with open('./data/city_region_utf8.json',encoding='utf-8') as f:\n",
    "    city_json = json.load(f)\n",
    "print(city_json,city_json.items())\n",
    "city_df = pd.DataFrame(list(city_json.items()),columns=['city','region'])\n",
    "city_df\n",
    "df_full = pd.merge(df4,city_df,on='city',how='left')\n",
    "df_full"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "29f07d6e-33c2-4c83-9896-56f64fa8b47b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full.to_csv('data/student_full.csv',index=False,encoding='utf-8-sig')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c111525-7c54-48be-8943-2d7176c5074f",
   "metadata": {},
   "source": [
    "## 2、数据清理\n",
    "### 2.1、缺失值的处理\n",
    "#### 2.1.1、查看分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "349f35bd-5a26-4b83-90c4-70bf859a5932",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "city             22\n",
       "region           22\n",
       "club             21\n",
       "Math             11\n",
       "Chinese           8\n",
       "absence_count     3\n",
       "Chemistry         2\n",
       "class             2\n",
       "age               2\n",
       "gender            2\n",
       "Physics           2\n",
       "register_date     2\n",
       "English           2\n",
       "room              2\n",
       "BMI               2\n",
       "head_teacher      2\n",
       "Biology           2\n",
       "height_cm         2\n",
       "weight_kg         2\n",
       "name              0\n",
       "student_id        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "df\n",
    "#查询哪一列的缺失值是最多\n",
    "df.isna().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a88c5f41-6b3a-4b8f-a7cf-ecb8f0d040a8",
   "metadata": {},
   "source": [
    "#### 2.1.2、缺失值city的处理\n",
    "先删除有问题的两个学生数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "006e5f96-f8d3-4ca0-a9fa-ec19292db990",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>gender</th>\n",
       "      <th>class</th>\n",
       "      <th>age</th>\n",
       "      <th>register_date</th>\n",
       "      <th>city</th>\n",
       "      <th>club</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Math</th>\n",
       "      <th>...</th>\n",
       "      <th>Physics</th>\n",
       "      <th>Chemistry</th>\n",
       "      <th>Biology</th>\n",
       "      <th>absence_count</th>\n",
       "      <th>head_teacher</th>\n",
       "      <th>room</th>\n",
       "      <th>BMI</th>\n",
       "      <th>height_cm</th>\n",
       "      <th>weight_kg</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23001</td>\n",
       "      <td>褚超琪</td>\n",
       "      <td>M</td>\n",
       "      <td>2B</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2024-09-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>合唱</td>\n",
       "      <td>83.2</td>\n",
       "      <td>104.5</td>\n",
       "      <td>...</td>\n",
       "      <td>103.4</td>\n",
       "      <td>106.1</td>\n",
       "      <td>77.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>张老师</td>\n",
       "      <td>2号楼-257</td>\n",
       "      <td>21.80</td>\n",
       "      <td>141.1</td>\n",
       "      <td>43.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23002</td>\n",
       "      <td>孙明丽</td>\n",
       "      <td>男</td>\n",
       "      <td>2B</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2024-08-25</td>\n",
       "      <td>昆明</td>\n",
       "      <td>篮球</td>\n",
       "      <td>74.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>67.7</td>\n",
       "      <td>87.8</td>\n",
       "      <td>81.7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>张老师</td>\n",
       "      <td>2号楼-257</td>\n",
       "      <td>17.14</td>\n",
       "      <td>142.5</td>\n",
       "      <td>34.8</td>\n",
       "      <td>滇中</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23003</td>\n",
       "      <td>冯鑫芸</td>\n",
       "      <td>M</td>\n",
       "      <td>2A</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2024-08-07</td>\n",
       "      <td>昆明</td>\n",
       "      <td>篮球</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58.5</td>\n",
       "      <td>...</td>\n",
       "      <td>69.1</td>\n",
       "      <td>57.5</td>\n",
       "      <td>83.4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>孔老师</td>\n",
       "      <td>2号楼-367</td>\n",
       "      <td>19.20</td>\n",
       "      <td>140.5</td>\n",
       "      <td>37.9</td>\n",
       "      <td>滇中</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23004</td>\n",
       "      <td>孔凯霖</td>\n",
       "      <td>女</td>\n",
       "      <td>3B</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2023-12-15</td>\n",
       "      <td>zhaotong</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76.7</td>\n",
       "      <td>69.7</td>\n",
       "      <td>...</td>\n",
       "      <td>77.2</td>\n",
       "      <td>71.1</td>\n",
       "      <td>71.9</td>\n",
       "      <td>4.0</td>\n",
       "      <td>王老师</td>\n",
       "      <td>3号楼-351</td>\n",
       "      <td>17.59</td>\n",
       "      <td>164.0</td>\n",
       "      <td>47.3</td>\n",
       "      <td>滇东北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23005</td>\n",
       "      <td>杨旭豪</td>\n",
       "      <td>女</td>\n",
       "      <td>4B</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2024-03-10</td>\n",
       "      <td>zhaotong</td>\n",
       "      <td>足球</td>\n",
       "      <td>67.9</td>\n",
       "      <td>66.8</td>\n",
       "      <td>...</td>\n",
       "      <td>65.6</td>\n",
       "      <td>56.6</td>\n",
       "      <td>75.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>李老师</td>\n",
       "      <td>4号楼-279</td>\n",
       "      <td>16.01</td>\n",
       "      <td>160.2</td>\n",
       "      <td>41.1</td>\n",
       "      <td>滇东北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>23118</td>\n",
       "      <td>冯楠玥</td>\n",
       "      <td>男</td>\n",
       "      <td>2A</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2024-03-30</td>\n",
       "      <td>曲靖</td>\n",
       "      <td>NaN</td>\n",
       "      <td>81.4</td>\n",
       "      <td>85.8</td>\n",
       "      <td>...</td>\n",
       "      <td>71.3</td>\n",
       "      <td>72.1</td>\n",
       "      <td>83.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>孔老师</td>\n",
       "      <td>2号楼-367</td>\n",
       "      <td>13.90</td>\n",
       "      <td>158.7</td>\n",
       "      <td>35.0</td>\n",
       "      <td>滇东北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>23119</td>\n",
       "      <td>陶鑫璐</td>\n",
       "      <td>女</td>\n",
       "      <td>3B</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2024-10-24</td>\n",
       "      <td>Kunming</td>\n",
       "      <td>书法</td>\n",
       "      <td>93.5</td>\n",
       "      <td>95.3</td>\n",
       "      <td>...</td>\n",
       "      <td>82.3</td>\n",
       "      <td>91.1</td>\n",
       "      <td>83.9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>王老师</td>\n",
       "      <td>3号楼-351</td>\n",
       "      <td>16.58</td>\n",
       "      <td>158.6</td>\n",
       "      <td>41.7</td>\n",
       "      <td>滇中</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>23120</td>\n",
       "      <td>赵宁睿</td>\n",
       "      <td>M</td>\n",
       "      <td>4A</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2024-04-09</td>\n",
       "      <td>Kunming</td>\n",
       "      <td>足球</td>\n",
       "      <td>84.7</td>\n",
       "      <td>72.2</td>\n",
       "      <td>...</td>\n",
       "      <td>67.8</td>\n",
       "      <td>72.3</td>\n",
       "      <td>81.6</td>\n",
       "      <td>3.0</td>\n",
       "      <td>王老师</td>\n",
       "      <td>4号楼-347</td>\n",
       "      <td>14.07</td>\n",
       "      <td>166.7</td>\n",
       "      <td>39.1</td>\n",
       "      <td>滇中</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>23011</td>\n",
       "      <td>孔雯璐</td>\n",
       "      <td>M</td>\n",
       "      <td>2A</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>zhaotong</td>\n",
       "      <td>编程</td>\n",
       "      <td>90.0</td>\n",
       "      <td>64.7</td>\n",
       "      <td>...</td>\n",
       "      <td>68.4</td>\n",
       "      <td>68.4</td>\n",
       "      <td>92.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>孔老师</td>\n",
       "      <td>2号楼-367</td>\n",
       "      <td>13.48</td>\n",
       "      <td>155.3</td>\n",
       "      <td>32.5</td>\n",
       "      <td>滇东北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>23011</td>\n",
       "      <td>孔雯璐</td>\n",
       "      <td>M</td>\n",
       "      <td>2A</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2024-09-23</td>\n",
       "      <td>zhaotong</td>\n",
       "      <td>编程</td>\n",
       "      <td>90.0</td>\n",
       "      <td>64.7</td>\n",
       "      <td>...</td>\n",
       "      <td>68.4</td>\n",
       "      <td>68.4</td>\n",
       "      <td>92.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>孔老师</td>\n",
       "      <td>2号楼-367</td>\n",
       "      <td>13.48</td>\n",
       "      <td>155.3</td>\n",
       "      <td>32.5</td>\n",
       "      <td>滇东北</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>123 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     student_id   name gender class   age register_date      city club  \\\n",
       "0         23001    褚超琪      M    2B   9.0    2024-09-28       NaN   合唱   \n",
       "1         23002    孙明丽      男    2B   9.0    2024-08-25        昆明   篮球   \n",
       "2         23003   冯鑫芸       M    2A  10.0    2024-08-07        昆明   篮球   \n",
       "3         23004    孔凯霖      女    3B  10.0    2023-12-15  zhaotong  NaN   \n",
       "4         23005    杨旭豪      女    4B  11.0    2024-03-10  zhaotong   足球   \n",
       "..          ...    ...    ...   ...   ...           ...       ...  ...   \n",
       "118       23118    冯楠玥      男    2A  10.0    2024-03-30        曲靖  NaN   \n",
       "119       23119    陶鑫璐      女    3B  10.0    2024-10-24   Kunming   书法   \n",
       "120       23120    赵宁睿      M    4A  12.0    2024-04-09   Kunming   足球   \n",
       "121       23011    孔雯璐      M    2A  10.0    2024-09-23  zhaotong   编程   \n",
       "122       23011    孔雯璐      M    2A  10.0    2024-09-23  zhaotong   编程   \n",
       "\n",
       "     Chinese   Math  ...  Physics  Chemistry  Biology  absence_count  \\\n",
       "0       83.2  104.5  ...    103.4      106.1     77.1            1.0   \n",
       "1       74.5    NaN  ...     67.7       87.8     81.7            4.0   \n",
       "2        NaN   58.5  ...     69.1       57.5     83.4            3.0   \n",
       "3       76.7   69.7  ...     77.2       71.1     71.9            4.0   \n",
       "4       67.9   66.8  ...     65.6       56.6     75.8            2.0   \n",
       "..       ...    ...  ...      ...        ...      ...            ...   \n",
       "118     81.4   85.8  ...     71.3       72.1     83.2            0.0   \n",
       "119     93.5   95.3  ...     82.3       91.1     83.9            1.0   \n",
       "120     84.7   72.2  ...     67.8       72.3     81.6            3.0   \n",
       "121     90.0   64.7  ...     68.4       68.4     92.9            2.0   \n",
       "122     90.0   64.7  ...     68.4       68.4     92.9            2.0   \n",
       "\n",
       "     head_teacher     room    BMI  height_cm  weight_kg  region  \n",
       "0             张老师  2号楼-257  21.80      141.1       43.4     NaN  \n",
       "1             张老师  2号楼-257  17.14      142.5       34.8      滇中  \n",
       "2             孔老师  2号楼-367  19.20      140.5       37.9      滇中  \n",
       "3             王老师  3号楼-351  17.59      164.0       47.3     滇东北  \n",
       "4             李老师  4号楼-279  16.01      160.2       41.1     滇东北  \n",
       "..            ...      ...    ...        ...        ...     ...  \n",
       "118           孔老师  2号楼-367  13.90      158.7       35.0     滇东北  \n",
       "119           王老师  3号楼-351  16.58      158.6       41.7      滇中  \n",
       "120           王老师  4号楼-347  14.07      166.7       39.1      滇中  \n",
       "121           孔老师  2号楼-367  13.48      155.3       32.5     滇东北  \n",
       "122           孔老师  2号楼-367  13.48      155.3       32.5     滇东北  \n",
       "\n",
       "[123 rows x 21 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "df\n",
    "df['na_count'] = df.isna().sum(axis=1)\n",
    "df\n",
    "df = df[df['na_count']<=10]\n",
    "df\n",
    "df = df.drop(columns=['na_count'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "f790a5be-7878-42c3-a7ad-71d83410f3b4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Nan' '昆明' 'Zhaotong' '曲靖' '昭通' 'Kunming']\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "df\n",
    "df['na_count'] = df.isna().sum(axis=1)\n",
    "df\n",
    "df = df[df['na_count']<=10]\n",
    "df\n",
    "df = df.drop(columns=['na_count'])\n",
    "df\n",
    "#1、处理city的问题，首先需要去掉空格\n",
    "city_str = df['city'].astype(str)\n",
    "df['city'] = city_str.str.strip().str.title()\n",
    "df\n",
    "print(df['city'].unique())\n",
    "#统一none的格式，变成真正的NaN\n",
    "df['city'] = df['city'].replace(['None','Nan','NaN','NoneType'],np.nan)\n",
    "df\n",
    "#看一下存在的唯一值\n",
    "df['city'].unique()\n",
    "#表现格式，把拼音变为文字\n",
    "df['city'] = df['city'].replace({\n",
    "    'Zhaotong':'昭通',\n",
    "    'Kunming':'昆明'\n",
    "})\n",
    "df['city'].unique()\n",
    "#删除就的region。\n",
    "df = df.drop(columns=['region'])\n",
    "df\n",
    "import json\n",
    "with open('./data/city_region_utf8.json',encoding='utf-8') as f:\n",
    "    city_json = json.load(f)\n",
    "city_df = pd.DataFrame(list(city_json.items()),columns=['city','region'])\n",
    "city_df\n",
    "df_ok = pd.merge(df,city_df,on='city',how='left')\n",
    "df_ok\n",
    "df_ok.isna().sum().sort_values(ascending=False)\n",
    "#最后统一将NaN填充为未知\n",
    "df_ok['city'] = df_ok['city'].fillna('未知')\n",
    "df_ok['region'] = df_ok['region'].fillna('未知区域')\n",
    "df_ok.isna().sum().sort_values(ascending=False)\n",
    "#保存数据\n",
    "df_ok.to_csv('data/student_full.csv',index=False,encoding='utf-8-sig')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9503c39-4e46-42fa-92d0-d5ab52022862",
   "metadata": {},
   "source": [
    "#### 2.1.3、club的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "30c07da4-3499-4d55-a6d6-046e6d34bd34",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "df = df.drop(columns=['Unnamed: 0'], errors='ignore')\n",
    "df\n",
    "#1、看一下唯一值\n",
    "df['club'].unique()\n",
    "#统一NaN的标准\n",
    "df['club'] = df['club'].replace(\n",
    "    ['None','nan',' ','NULL','Null','NaN'],np.nan\n",
    ")\n",
    "df\n",
    "#这个的缺失原因很有可能是学生没有加入任何社团，有可能是真实的缺失，一般来说可以不用修复，可以直接填充成无社团\n",
    "df['club'] = df['club'].fillna('无社团')\n",
    "df\n",
    "df['club'].value_counts()\n",
    "df.to_csv('./data/student_full.csv',encoding='utf-8',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d20f5318-056c-4eed-9130-b2b6a70d71fe",
   "metadata": {},
   "source": [
    "#### 2.1.4、groupby和transform介绍"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "769bcdd0-b0e3-4be2-b234-b17959c70d83",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  class  scores\n",
      "0     A    80.0\n",
      "1     A    90.0\n",
      "2     B     NaN\n",
      "3     B    80.0\n",
      "4     A     NaN\n",
      "class\n",
      "A    85.0\n",
      "B    80.0\n",
      "Name: scores, dtype: float64\n",
      "0    85.0\n",
      "1    85.0\n",
      "2    80.0\n",
      "3    80.0\n",
      "4    85.0\n",
      "Name: scores, dtype: float64\n",
      "  class  scores  class_mean\n",
      "0     A    80.0        85.0\n",
      "1     A    90.0        85.0\n",
      "2     B    80.0        80.0\n",
      "3     B    80.0        80.0\n",
      "4     A    85.0        85.0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>class</th>\n",
       "      <th>scores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  class  scores\n",
       "0     A    80.0\n",
       "1     A    90.0\n",
       "2     B    80.0\n",
       "3     B    80.0\n",
       "4     A    85.0"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "#需要进行分组填充\n",
    "df1 = pd.DataFrame({\n",
    "    'class':['A','A','B','B','A'],\n",
    "    'scores':[80,90,np.nan,80,np.nan]\n",
    "})\n",
    "print(df1)\n",
    "print(df1.groupby('class')['scores'].mean())\n",
    "#使用transform可以保存原有的结构\n",
    "print(df1.groupby('class')['scores'].transform('mean'))\n",
    "df1['class_mean'] = df1.groupby('class')['scores'].transform('mean')\n",
    "df1['scores'] = df1['scores'].fillna(df1['class_mean'])\n",
    "print(df1)\n",
    "\n",
    "#另外一种处理方式\n",
    "df2 = pd.DataFrame({\n",
    "    'class':['A','A','B','B','A'],\n",
    "    'scores':[80,90,np.nan,80,np.nan]\n",
    "})\n",
    "#x表示某一列数据，是分好组的那一列数据\n",
    "# def fn(x):\n",
    "#     return x.fillna(x.mean())\n",
    "# df2['scores'] = df2.groupby('class')['scores'].transform(fn)\n",
    "# df2\n",
    "#可以简化成如下步骤\n",
    "df2['scores'] = df2.groupby('class')['scores'].transform(\n",
    "    lambda x:x.fillna(x.mean())\n",
    ")\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe8f1ec4-f4b1-415b-a756-9a93ba741936",
   "metadata": {},
   "source": [
    "#### 2.1.5、处理成绩的缺失值\n",
    "成绩是典型的 数值型数据（numeric features）。对于数值缺失，有三种常见的填充策略：\n",
    "| 填充方式                     | 优点            | 缺点            | 是否适合成绩？   |\n",
    "| ------------------------ | ------------- | ------------- | --------- |\n",
    "| **全局平均值填充**              | 简单            | 混合不同班级，不准确    | ❌ 不推荐     |\n",
    "| **按班级平均值填充（class mean）** | 同班学业水平接近，非常合理 | 如果班级人数太少，会不稳定 | ✔ 推荐（最合理） |\n",
    "| **按科目中位数填充**             | 稳定，不受极端值影响    | 忽略班级结构        | ✔ 可作为备用方案 |\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "bfdfd8e5-54fe-4c8d-a27b-3e4d6e670006",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Chinese       9\n",
      "Math         11\n",
      "English       3\n",
      "Physics       7\n",
      "Chemistry     2\n",
      "Biology       1\n",
      "dtype: int64\n",
      "Empty DataFrame\n",
      "Columns: [student_id, height_cm]\n",
      "Index: []\n",
      "   student_id  weight_kg\n",
      "8       23009       19.1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Math             11\n",
       "Chinese           9\n",
       "Physics           7\n",
       "English           3\n",
       "Chemistry         2\n",
       "absence_count     1\n",
       "weight_kg         1\n",
       "Biology           1\n",
       "age               0\n",
       "student_id        0\n",
       "class             0\n",
       "gender            0\n",
       "name              0\n",
       "city              0\n",
       "register_date     0\n",
       "club              0\n",
       "head_teacher      0\n",
       "room              0\n",
       "BMI               0\n",
       "height_cm         0\n",
       "region            0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#填充成绩之前应该先处理异常值\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "scores = ['Chinese','Math','English','Physics','Chemistry','Biology']\n",
    "df[scores].isna().sum()\n",
    "# print(df[[df.columns[0],'Chinese']])\n",
    "df.loc[0,'Chinese'] = 199\n",
    "df.loc[1,'Math'] = -10\n",
    "df.loc[2,'English'] = 222\n",
    "#把异常值直接变成NaN\n",
    "bad_values = (df[scores]>100) | (df[scores]<0)\n",
    "# bad_values查询有哪些值是有问题的\n",
    "df[scores][bad_values]\n",
    "#把异常值修改为NaN，只能一行一行的修改\n",
    "df.loc[(df['Chinese']<0)|(df['Chinese']>100),'Chinese'] = np.nan\n",
    "df\n",
    "for s in scores:\n",
    "    df.loc[(df[s]<0)|(df[s]>100),s] = np.nan\n",
    "df\n",
    "#把异常值首先设置为null\n",
    "print(df[scores].isna().sum())\n",
    "#随便把身高和体重都处理了\n",
    "#检查一下身高有问题的数据\n",
    "print(df.loc[(df['height_cm']<100)|(df['height_cm']>190),['student_id','height_cm']])\n",
    "print(df.loc[(df['weight_kg']<20)|(df['weight_kg']>120),['student_id','weight_kg']])\n",
    "df.loc[(df['weight_kg']<20)|(df['weight_kg']>120),['weight_kg']]  = np.nan\n",
    "df.loc[(df['height_cm']<100)|(df['height_cm']>190),['height_cm']] = np.nan\n",
    "df.isna().sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "97563af9-1b49-44f3-81a0-ed29cfe9b878",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pandas as pd\n",
    "# import numpy as np\n",
    "# df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "# df[['Chinese','Math','English','Physics','Chemistry','Biology']].isna().sum()\n",
    "#语文缺失6个，数学缺失9个\n",
    "#接下来按照班级的均值填充\n",
    "#1、填充语文\n",
    "# df['Chinese'] = df.groupby('class')['Chinese'].transform(\n",
    "#     lambda x: x.fillna(x.mean())\n",
    "# )\n",
    "# df['Chinese'].isna().sum()\n",
    "scores = ['Chinese','Math','English','Physics','Chemistry','Biology']\n",
    "for s in scores:\n",
    "    df[s] = df.groupby('class')[s].transform(\n",
    "        lambda x: x.fillna(x.mean())\n",
    "    )\n",
    "df.isna().sum()\n",
    "df.to_csv('./data/student_full.csv',index=False,encoding='utf-8-sig')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8070306d-3bb0-4f4f-b4c8-963d87dcb16b",
   "metadata": {},
   "source": [
    "#### 2.1.6、其他缺失值的处理\n",
    "absence_count表示缺勤次数，直接填充为0即可，有可能是忘记录入了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "efa90f7d-f6eb-4198-8ea7-af6627b71dc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv('./data/student_full.csv',encoding='utf-8')\n",
    "df.isna().sum()\n",
    "df['absence_count'] = df['absence_count'].fillna(0).astype(int)\n",
    "df.isna().sum()\n",
    "df\n",
    "#身高和体重都用班级平均值来填充\n",
    "df['height_cm'] = df.groupby('class')['height_cm'].transform(\n",
    "    lambda x:x.fillna(x.mean())\n",
    ")\n",
    "df['weight_kg'] = df.groupby('class')['weight_kg'].transform(\n",
    "    lambda x:x.fillna(x.mean())\n",
    ")\n",
    "df.isna().sum()\n",
    "#更新BMI的数据\n",
    "df['BMI'] = df['weight_kg'] / (df['height_cm']/100) ** 2\n",
    "df['BMI'] = df['BMI'].round(2)\n",
    "scores = ['Chinese','Math','English','Physics','Chemistry','Biology']\n",
    "df[scores] = df[scores].round(2)\n",
    "df\n",
    "df.to_csv('data/student_full.csv',index=False,encoding='utf-8-sig')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ef6789f-40e2-4a3b-8b40-c9b3f22e4d84",
   "metadata": {},
   "source": [
    "#### 2.1.7、作业\n",
    "- Part 1：数据加载与初步检查\n",
    "使用 Pandas 读取四张表。\n",
    "分别打印：行数、列数、列名（columns），缺失值统计（isna().sum()）\n",
    "- Part 2：Customers 表清洗\n",
    "    - 2.1 删除重复行\n",
    " \n",
    "      使用 duplicated() 找到重复顾客记录。使用 drop_duplicates() 删除。\n",
    "\n",
    "    - 2.2 清洗 name 字段\n",
    " \n",
    "      去除前后空格（str.strip()）\n",
    " \n",
    "      统计空姓名（name == \"\" 或 NaN）\n",
    "\n",
    "    - 2.3 清洗 age 字段\n",
    "\n",
    "      找出年龄异常值：age < 0,age > 120,age 为 NaN\n",
    " \n",
    "      处理方式：异常值 → 填为 NaN\n",
    " \n",
    "      最终用 年龄中位数（median） 填充缺失值。\n",
    "\n",
    "    - 2.4 清洗 city 字段\n",
    " \n",
    "      把 \"\", \"None\", \"nan\" → 统一替换为 NaN\n",
    " \n",
    "      用该列的 众数（mode） 填充缺失值\n",
    " \n",
    "- Part 3：Products 表清洗\n",
    "    - 3.1 清洗 price找到并处理：价格 < 0，价格 > 100000，价格为 NaN，清洗后使用 中位数 填充缺失值。\n",
    "\n",
    "    - 3.2 清洗产品名称：名称为空字符串（\"\"）的 → 填为 \"Unknown Product\"\n",
    "\n",
    "- Part 4：Orders 表清洗\n",
    "    - 4.1 清洗 customer_id\n",
    "\n",
    "        无效情况包括：空字符串，NaN，不存在于 customers 表中的 customer_id,处理方式：删除这些订单行\n",
    "\n",
    "    - 4.2 清洗 order_date\n",
    "    \n",
    "      用 to_datetime(errors='coerce') 将非法日期转换为 NaN,删除日期为 NaN 的订单,最终要求日期列为 datetime 类型\n",
    " \n",
    "- Part 5：Order Items 清洗\n",
    "    - 5.1 清洗 product_id\n",
    "\n",
    "      删除以下情况：不在 products 表中,\"P999\"\n",
    "\n",
    "    - 5.2 清洗 quantity\n",
    "\n",
    "      非法情况：0,负数,NaN,处理方式：将这些值设为 NaN,再用 1 填充（最小购买数量）\n",
    " \n",
    "- Part 6：多表合并\n",
    "\n",
    "    - 按顺序合并成最终表 final_df：order_items 合并 products,合并 orders,合并 customers\n",
    "\n",
    "    - 注意事项：\n",
    " \n",
    "      要保证 merge 不会产生无意义的数据\n",
    " \n",
    "      合并方式合理选择（inner / left）\n",
    " \n",
    "      最终表应包含：顾客信息,产品信息,订单信息,订单数量,金额字段：\n",
    " \n",
    "    - 根据数据情况看是否需要进行二次清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "067f3f9b-4263-4cfd-95a8-2133468baf59",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  student_id name  Math  Chinese\n",
      "0       S001   张三    88       90\n",
      "1       S002  李四1    92       85\n",
      "2       S003   王五    75       70\n",
      "3       S001   张三    88       90\n",
      "4       S004   赵六    95       88\n",
      "5       S002   李四    92       85\n",
      "6       S005   田七    80       78\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>Math</th>\n",
       "      <th>Chinese</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S001</td>\n",
       "      <td>张三</td>\n",
       "      <td>88</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S002</td>\n",
       "      <td>李四1</td>\n",
       "      <td>92</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S003</td>\n",
       "      <td>王五</td>\n",
       "      <td>75</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S004</td>\n",
       "      <td>赵六</td>\n",
       "      <td>95</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S005</td>\n",
       "      <td>田七</td>\n",
       "      <td>80</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id name  Math  Chinese\n",
       "0       S001   张三    88       90\n",
       "1       S002  李四1    92       85\n",
       "2       S003   王五    75       70\n",
       "4       S004   赵六    95       88\n",
       "6       S005   田七    80       78"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.DataFrame({\n",
    "    \"student_id\": [\"S001\", \"S002\", \"S003\", \"S001\", \"S004\", \"S002\", \"S005\"],\n",
    "    \"name\": [\"张三\", \"李四1\", \"王五\", \"张三\", \"赵六\", \"李四\", \"田七\"],\n",
    "    \"Math\": [88, 92, 75, 88, 95, 92, 80],\n",
    "    \"Chinese\": [90, 85, 70, 90, 88, 85, 78]\n",
    "})\n",
    "\n",
    "print(df)\n",
    "df['student_id'].duplicated().sum()\n",
    "df = df.drop_duplicates(subset=['student_id'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a018ba6f-4670-4dad-9932-70753cd809e8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
